<!DOCTYPE html>
<html>
<head>
    <title>006.数据操作-简单查询</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="selected">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 006.数据操作-简单查询 </h1>
            <div class="doc_post"> <p class="md_block md_has_block_below md_has_block_below_blockquote">

<div class="toc"><ul>
<li>
<a href="#toc_0">006.数据操作-简单查询</a>
<ul>
<li>
<a href="#toc_1">简单无条件查询</a>
</li>
<li>
<a href="#toc_2">条件查询 WHIERE</a>
<ul>
<li>
<a href="#toc_3">比较查询 = &gt; &gt;= < &lt;= != &lt;></a>
</li>
<li>
<a href="#toc_4">NULL查询 IS NULL   IS NOT NULL</a>
</li>
<li>
<a href="#toc_5">多条件结合查询  AND  OR</a>
</li>
<li>
<a href="#toc_6">指定范围查询 [NOT] BETWEEN 条件 AND 条件</a>
</li>
<li>
<a href="#toc_7">指定集合内查询 IN , NOT IN</a>
</li>
<li>
<a href="#toc_8">模糊查询 LIKE, NOT LIKE</a>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">简单无条件查询</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 1.不指定列查询(查询所有列),* 表示所有列</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- 2.指定列名查询</span>
<span class="k">SELECT</span> <span class="n">uname</span><span class="p">,</span><span class="n">mobile</span><span class="p">,</span><span class="n">qq</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- 3.用完整的数据为名,表名,列名 查询</span>

    <span class="c1">-- 3.1 使用完整的表名跨库查询</span>
    <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">student</span><span class="p">.</span><span class="n">dbo</span><span class="p">.</span><span class="n">stu_info</span><span class="p">;</span>  <span class="c1">-- 只要有权限,可以跨库查询,用完整的表名(数据库名.架构名.表名)就行.</span>
    
    <span class="c1">-- 3.2 指定显示列上用完整的表名查询(目前看来多余的,后面多表结合查询时有用)</span>
    <span class="k">SELECT</span> <span class="n">student</span><span class="p">.</span><span class="n">dbo</span><span class="p">.</span><span class="n">stu_info</span><span class="p">.</span><span class="n">uname</span><span class="p">,</span><span class="n">stu_info</span><span class="p">.</span><span class="n">qq</span> <span class="k">FROM</span> <span class="n">student</span><span class="p">.</span><span class="n">dbo</span><span class="p">.</span><span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- 4.给表,列指定别名 AS 可以省略不写,如果自定义名称中有空格等,用单或双引号或中括号包起来</span>
    <span class="c1">-- 4.1给表起别名</span>
    <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">AS</span> <span class="err">学生表</span><span class="p">;</span>
    <span class="c1">-- 4.2给列起别名</span>
    <span class="k">SELECT</span> <span class="n">uname</span> <span class="k">AS</span> <span class="err">姓名</span><span class="p">,</span><span class="n">pwd</span> <span class="s1">&#39;密 码&#39;</span><span class="p">,</span><span class="n">qq</span> <span class="k">AS</span> <span class="n">QQ</span><span class="err">号码</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="err">学生表</span><span class="p">;</span>

<span class="c1">-- 5.查询时自定义列</span>
<span class="k">SELECT</span> <span class="n">uname</span><span class="p">,</span><span class="n">pwd</span><span class="p">,</span><span class="n">qq</span><span class="p">,</span><span class="s1">&#39;真的无聊&#39;</span> <span class="k">AS</span> <span class="s1">&#39;无聊列&#39;</span><span class="p">,</span><span class="n">class_id</span><span class="o">+</span><span class="s1">&#39;班的-&#39;</span><span class="o">+</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- 6. 在列上使用函数</span>
<span class="k">SELECT</span> <span class="n">uname</span><span class="p">,</span><span class="k">substring</span><span class="p">(</span><span class="n">code</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">6</span><span class="p">)</span><span class="o">+</span><span class="s1">&#39;****&#39;</span><span class="o">+</span><span class="k">substring</span><span class="p">(</span><span class="n">code</span><span class="p">,</span><span class="mi">15</span><span class="p">,</span><span class="mi">7</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span>

<span class="c1">-- 7.在列上使用case when then [else] end 语句</span>
<span class="k">SELECT</span> 
<span class="n">uname</span> <span class="k">AS</span> <span class="err">姓名</span><span class="p">,</span>
<span class="k">CASE</span> 
    <span class="k">WHEN</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">0</span> 
        <span class="k">THEN</span> <span class="s1">&#39;女&#39;</span> 
    <span class="k">WHEN</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">1</span> 
        <span class="k">THEN</span> <span class="s1">&#39;男&#39;</span> 
    <span class="k">ELSE</span> <span class="s1">&#39;泰国人&#39;</span> 
<span class="k">END</span> <span class="k">AS</span> <span class="err">性别</span> 
<span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- 8.查询结果去重复记录 DISTINCT</span>
<span class="k">SELECT</span> <span class="k">DISTINCT</span> <span class="n">sex</span><span class="p">,</span><span class="n">class_id</span> <span class="k">FROM</span> <span class="n">stu_info</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_2">条件查询 WHIERE</h2>

<h3 id="toc_3">比较查询 = &gt; &gt;= &lt; &lt;= != &lt;&gt;</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">id</span><span class="o">=</span><span class="mi">1</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">id</span><span class="o">&gt;</span><span class="mi">1</span><span class="p">;</span>
<span class="c1">-- 语法一样,测试各符号</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">test</span> <span class="o">=</span> <span class="k">null</span><span class="p">;</span> <span class="c1">-- 查不出任何数据</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_4">NULL查询 IS NULL   IS NOT NULL</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 切记不能用 = NULL 来查询,在MYSQL中可以用 &lt;=&gt;来查询</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">test</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">;</span>
<span class="c1">-- 查询非NULL </span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">test</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">;</span> 
</pre></div>

<!--block_code_end-->
<h3 id="toc_5">多条件结合查询  AND  OR</h3>

<p class="md_block">
    <span class="md_line">跟程序语言差不多,多个条件进行判断时,要用OR 或 AND连接多个条件.</span>
</p>


<blockquote>

<p class="md_block">
    <span class="md_line">注意:在MYSQL中,也需注意同时用OR与AND的优选级,如搞不清楚,就加小括号括起来.AND优先.</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--  多条件查询</span>
    <span class="c1">-- 查询11班的男生</span>
    <span class="k">SELECT</span> <span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">class_id</span> <span class="o">=</span> <span class="mi">11</span> <span class="k">AND</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>

    <span class="c1">-- 查询9班10班中的女生</span>
    <span class="k">SELECT</span> <span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="p">(</span><span class="n">class_id</span> <span class="o">=</span> <span class="mi">9</span> <span class="k">OR</span> <span class="n">class_id</span> <span class="o">=</span> <span class="mi">10</span><span class="p">)</span> <span class="k">AND</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">0</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_6">指定范围查询 [NOT] BETWEEN 条件 AND 条件</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span>    <span class="c1">-- 某个字段区间查询(一般用于数字与日期时间)</span>
    <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">&gt;=</span> <span class="mi">5</span> <span class="k">AND</span> <span class="n">id</span> <span class="o">&lt;=</span> <span class="mi">10</span><span class="p">;</span>
    <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">&lt;</span> <span class="mi">5</span> <span class="k">OR</span> <span class="n">id</span> <span class="o">&gt;</span> <span class="mi">10</span><span class="p">;</span>
    <span class="c1">-- between的写法(注意包含最小值与最大值).</span>
    <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="k">BETWEEN</span> <span class="mi">5</span> <span class="k">AND</span> <span class="mi">10</span><span class="p">;</span> <span class="c1">-- 等同于:id &gt;= 5 AND id &lt;= 10;</span>
    <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="k">NOT</span> <span class="k">BETWEEN</span> <span class="mi">5</span> <span class="k">AND</span> <span class="mi">10</span><span class="p">;</span> <span class="c1">-- 等同于:id &lt; 5 OR id &gt; 10;</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_7">指定集合内查询 IN , NOT IN</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- IN:在集合内</span>
<span class="k">SELECT</span> <span class="n">uname</span><span class="p">,</span><span class="n">class_id</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">class_id</span> <span class="k">IN</span><span class="p">(</span><span class="mi">9</span><span class="p">,</span><span class="mi">10</span><span class="p">,</span><span class="mi">11</span><span class="p">);</span>

<span class="c1">-- NOT IN:不在集合内</span>
<span class="k">SELECT</span> <span class="n">uname</span><span class="p">,</span><span class="n">class_id</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">uname</span> <span class="k">NOT</span> <span class="k">IN</span><span class="p">(</span><span class="s1">&#39;霍德明&#39;</span><span class="p">,</span><span class="s1">&#39;邓陆&#39;</span><span class="p">,</span><span class="s1">&#39;李海源&#39;</span><span class="p">,</span><span class="s1">&#39;李金涛&#39;</span><span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_8">模糊查询 LIKE, NOT LIKE</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- %百分号:0个1个或多个(任意字符);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">uname</span> <span class="k">LIKE</span> <span class="s1">&#39;周%&#39;</span><span class="p">;</span>   <span class="c1">-- 只有右边有%,表示查询以周开始的(姓周的)用户</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">uname</span> <span class="k">LIKE</span> <span class="s1">&#39;%文%&#39;</span><span class="p">;</span> <span class="c1">-- 只要文本中包含&#39;文&#39;字的都可以查出来</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">uname</span> <span class="k">LIKE</span> <span class="s1">&#39;%文&#39;</span><span class="p">;</span> <span class="c1">-- 只要文本中以文结尾&#39;文&#39;字的都可以查出来</span>
<span class="c1">-- _下划线:表示1个字符</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test</span> <span class="k">WHERE</span> <span class="n">uname</span> <span class="k">LIKE</span> <span class="s1">&#39;周__&#39;</span><span class="p">;</span> <span class="c1">-- 用了2个下划线,表示姓周,且姓名三个字.</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>